import pymysql
from multiprocessing.pool import Pool


class Mysql(object):
    # port必须为init类型
    def __init__(self, ipaddr, user, passwd, port):
        self.host = ipaddr
        self.user = user
        self.passwd = passwd
        self.port = int(port)
        # self.db_name = db_name

    def SelectDatabase(self, data):

        try:
            conn = pymysql.connect(
                host=self.host,
                user=self.user,
                passwd=self.passwd,
                port=self.port,
                database=data[1],
                charset='utf8',
                cursorclass=pymysql.cursors.DictCursor
            )
            cur = conn.cursor()
            sql = data[0]
            cur.execute(sql)
            res = cur.fetchall()
            cur.close()
            conn.close()
            return res
        except Exception as e:
            # print(e)
            return False
    
    
    def GetAllDBTables(self):
        # 获取所有数据库，排除自带的数据库
        exclude_list = ["sys", "information_schema", "mysql", "performance_schema", "test"]
        sql_database = "show databases"
        data=[sql_database,"mysql"]
        db_res = self.SelectDatabase(data)
        if not db_res:
            err_log = "连接%s数据库失败"
            return err_log
            
        all_db_data = []
        for dbname_list in db_res:
            # 清洗数据库名
            db_name = dbname_list['Database']
            if db_name not in exclude_list:
                
                # 重建获取表名sql
                table_sql = "show tables"
                data = [table_sql,db_name]
                tables_res = self.SelectDatabase(data)
                # 清洗表名
                exclude_data = "Tables_in_"+db_name
                for table_list in tables_res:
                    dbtables_list = []
                    table_name = table_list[exclude_data]
                    # 重建获取字段数据sql
                    sql = "desc "+db_name+"."+table_name
                    dbtables_list.append(sql)
                    dbtables_list.append(db_name)
                    all_db_data.append(dbtables_list)    
        return all_db_data
        
        
    def main(self):
        data = self.GetAllDBTables()
        
        pool = Pool(processes=3)
        dbtables_res = pool.map(self.SelectDatabase,data)
        pool.close()
        pool.join()
        print(dbtables_res)
   
if __name__ == '__main__':
    ipaddr = "127.0.0.1"
    user = "root"
    passwd = "123456"
    port = 3306
    # db_name = "mysql"
    
    obj = Mysql(ipaddr, user, passwd, port)
    all_db_list = obj.main()

# ConnectMySQL(ipaddr="127.0.0.1", user="root", password="123456", port=3306)
